package com.hgkj.cloud.customer.util;

import com.hgkj.cloud.base.util.FileUtil;
import lombok.Builder;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.DecimalFormat;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.*;

@Slf4j
public class ExcelPoiUtil {

    public static void createAndDownload(HttpServletResponse response, String filename,
                                              String title, String sheetName,
                                              String titleKeys, List<Map<String, Object>> dataList,
                                              Integer pageSize, Boolean isXlsx) throws Exception {
        if (!isXlsx) {//.xls
            createAndDownloadExcel(response, filename, title, sheetName, titleKeys, dataList, pageSize);
        } else {//.xlsx
            createAndDownloadXExcel(response, filename, title, sheetName, titleKeys, dataList, pageSize);
        }
    }
    /**
     * 生成excel xls格式并下载
     * @param response 响应
     * @param filename 下载文件名称
     * @param title 表格标题
     * @param sheetName 工作区标题
     * @param titleKeys 表格标题和数据列名对应串
     * @param dataList 数据
     * @param pageSize 每页数据
     * @throws Exception
     */
    public static void createAndDownloadExcel(HttpServletResponse response, String filename,
                                              String title, String sheetName,
                                              String titleKeys, List<Map<String, Object>> dataList,
                                              Integer pageSize) throws Exception {
        String filepath = createExcel(title, sheetName, titleKeys, dataList, pageSize);
        FileUtil.downloadFile(response, filename, filepath, true);
    }

    public static String createExcel(String title, String sheetName, String titleKeys, List<Map<String, Object>> dataList,
                                     Integer pageSize) throws Exception {
        List<DmForm> orderlyColumnList = getOrderlyColumnListByParam(titleKeys);
        // 数据键值转换为大写存储
        List<Map<String, Object>> data = convertDataKeyUpperCase(dataList);
        // 临时文件路径
        String savepath = createTmpFile(".xls");
        log.info("----- addDataToExcel file: " + savepath + " , data size:" + data.size() + " ----- ");
        HSSFWorkbook book = new HSSFWorkbook();
        HSSFSheet sheet = book.createSheet(sheetName);
        int rowIndex = 0;
        if (title != null) {
            HSSFRow row = sheet.createRow(rowIndex);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, orderlyColumnList.size() - 1));
            HSSFCell cell = row.createCell(0);
            cell.setCellValue(title);
            HSSFFont font = book.createFont();
            font.setFontName("Arial");
            font.setFontHeightInPoints((short) 13);//设置字体大小
            HSSFCellStyle cellStyle = book.createCellStyle();
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cell.setCellStyle(cellStyle);
            row.setHeight((short) 400);
            rowIndex++;
        }
        HSSFRow row = sheet.createRow(rowIndex++);
        int index = 0;
        for (DmForm dmForm : orderlyColumnList) {
            HSSFCell cell = row.createCell(index++);
            cell.setCellValue(dmForm.getName());
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(savepath);
            // 写入数据到输出文件中
            writeDataToXlsFile(book, sheet, sheetName, orderlyColumnList, data, outputStream, rowIndex + 1, pageSize);
        } finally {
            closeBook(book);
            closeOs(outputStream);
        }
        return savepath;
    }

    private static void writeDataToXlsFile(HSSFWorkbook workbook, HSSFSheet sheet, String sheetName,
                                           List<DmForm> orderlyColumnList,
                                           List<Map<String, Object>> data, OutputStream outputStream,
                                           int startWriteLine, Integer sheetPageSize) throws Exception {
        sheetPageSize = sheetPageSize == null ? 500 : sheetPageSize;
        if (startWriteLine + sheetPageSize >= 65535) {
            throw new Exception("非法参数：写入行+页数太大");
        }
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        HSSFDataFormat format = workbook.createDataFormat();
        cellStyle2.setDataFormat(format.getFormat("@"));
        int num = 0;
        int totalNo = 1;
        int sheet_index = 1;
        HSSFRow startWriteRow = sheet.getRow(startWriteLine - 1);
        for (Iterator<Map<String, Object>> iterator = data.iterator(); iterator.hasNext(); ) {
            Map<String, Object> dataMap = iterator.next();
            if (num >= sheetPageSize) {//如果数据量超过页码数则新建一个sheet
                HSSFSheet sheet1 = workbook.createSheet(sheetName + "(" + (++sheet_index) + ")");
                //合并区域处理
                mergerRegion(sheet, sheet1);
                // 设置表头
                for (int i = 0; i < startWriteLine; i++) {
                    HSSFRow row = sheet1.createRow(i);
                    HSSFRow row_master = sheet.getRow(i);
                    copyRow(workbook, row_master, row, true);
                    //设置行高
                    row.setHeight(row_master.getHeight());
                }
                //设置列宽
                for (int i = 0; i < orderlyColumnList.size(); i++) {
                    sheet1.setColumnWidth(i, sheet.getColumnWidth(i));
                }
                sheet = sheet1;
                num = 0;
            }
            HSSFRow row = sheet.getRow(startWriteLine - 1 + num);
            if (row == null) {
                row = sheet.createRow(startWriteLine - 1 + num);
            }
            for (int i = 0; i < orderlyColumnList.size(); i++) {
                HSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cell = row.createCell(i);
                }
                cell.setCellType(CellType.STRING);//设置为字符串类型
                if (startWriteRow != null && startWriteRow.getCell(i) != null) {
                    cell.setCellStyle(startWriteRow.getCell(i).getCellStyle());//设置样式
                } else {
                    cell.setCellStyle(cellStyle2);
                }
                DmForm dmForm = orderlyColumnList.get(i);
                if (dmForm != null && "序号".equals(dmForm.getName())) {
                    if (dmForm.getCode() == null || dataMap.get(dmForm.getCode()) == null) {
                        cell.setCellValue(totalNo++);
                        continue;
                    }
                }
                if (dmForm == null || dmForm.getName() == null || dmForm.getCode() == null) {
                    cell.setCellValue("");
                    continue;
                }
                Object o = dataMap.get(dmForm.getCode());
                cell.setCellValue((o == null || o.equals("null")) ? "" : o.toString());//灌入值
            }
            num++;
        }
        workbook.write(outputStream);
        outputStream.flush();
    }

    private static String createTmpFile(String suffix) {
        // 创建临时输出文件
        String filename = UUID.randomUUID().toString() + suffix;
        String saveDir = System.getProperty("user.dir") + File.separator + "temp";
        File pa = new File(saveDir);
        if (!pa.exists()) {
            pa.mkdirs();
        }
        // 临时文件路径
        String savepath = saveDir + File.separator + filename;
        return savepath;
    }

    private static List<DmForm> getOrderlyColumnListByParam(String titleKeys) {
        String[] ttKeys = titleKeys.replaceAll("　", "")
                .replaceAll("，", ",")
                .replaceAll(" ", "").split(",");
        List<DmForm> orderlyColumnList = new ArrayList<>();
        for (String keys : ttKeys) {
            String[] arr = keys.split("=");
            DmForm dmForm = DmForm.builder().name(arr[0]).code(arr[1].toUpperCase()).build();
            orderlyColumnList.add(dmForm);
        }
        return orderlyColumnList;
    }

//    public static void main(String[] args) throws Exception {
//        System.out.println("-------------aaaaaaaaaaaaaaa-------------");
//        aa();
//        System.out.println("-------------bbbbbbbbbbbbbbbbb----------");
//        bb();
//    }

    private static void bb() throws Exception {
        String sheetName = "张三的粉丝用户";
        String titleKeys = "序号=XH, 用户名 = name,  性别 = sex，组织机构 = ORG， 生成时间 = ptime";
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        for (int m = 0; m < 52; m++) {
            int finalM = m;
            dataList.add(new HashMap<String, Object>() {{
                put("Name", "张三" + finalM);
                put("sex", "1");
                put("ORG", "机构" + finalM);
                put("ptime", finalM % 3 == 0 ? new Date() : "2013-11-02");
            }});
        }
        String filePath = createExcel("张三的粉丝", sheetName, titleKeys, dataList, 11);
        System.out.println(filePath);
    }

    private static void aa() throws Exception {
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        for (int m = 0; m < 52; m++) {
            int finalM = m;
            dataList.add(new HashMap<String, Object>() {{
                put("Name", "张三" + finalM);
                put("sex", "1");
                put("ORG", "机构" + finalM);
                put("ptime", finalM % 3 == 0 ? new Date() : "2013-11-02");
            }});
        }
        String tempatexml = "xls_template/warning_report_user.xls";
        Map<String, Object> cnnameKeyMap = new HashMap<String, Object>() {{
            put("用户名", "name");
            put("性别", "sex");
            put("组织机构", "ORG");
            put("生成时间", "ptime");
        }};
        String filePath = addDataToExcel(tempatexml, dataList, cnnameKeyMap,
                2, 3, 23);
        File file = new File(filePath);
    }

    private static void closeBook(HSSFWorkbook book) {
        try {
            if (book != null)
                book.close();
        } catch (Exception e) {
        }
    }

    private static void closeBook(SXSSFWorkbook book) {
        try {
            if (book != null)
                book.close();
        } catch (Exception e) {
        }
    }


    private static void closeIs(InputStream inputStream) {
        if (inputStream != null) {
            try {
                inputStream.close();
            } catch (IOException e) {
            }
        }
    }

    private static void closeOs(OutputStream outputStream) {
        if (outputStream != null) {
            try {
                outputStream.close();
            } catch (IOException e) {
            }
        }
    }

    /**
     * 添加数据并生成excel
     *
     * @param templateXls    模板文件excel resources目录里路径
     * @param dataList       数据
     * @param cnnameKeyMap   列名对应键值map（格式:  key: 中文名称, value: 可匹配的大写数据查询列名）
     * @param titleLine      标题行，至少1开始
     * @param startWriteLine 内容开始写入行， 至少2开始
     * @param sheetPageSize  每页多少数据，默认一页500条
     * @return 临时文件地址
     */
    public static String addDataToExcel(String templateXls, List<Map<String, Object>> dataList,
                                        Map<String, Object> cnnameKeyMap,
                                        int titleLine, int startWriteLine,
                                        Integer sheetPageSize) throws Exception {
        InputStream inputStream = null;
        try {
            inputStream = ExcelPoiUtil.class.getClassLoader().getResourceAsStream(templateXls);
            return addDataToExcel(inputStream, dataList, cnnameKeyMap, titleLine, startWriteLine, sheetPageSize);
        } finally {
            closeIs(inputStream);
        }
    }

    /**
     * 添加数据并生成excel
     *
     * @param inputStream    模板文件输入流
     * @param dataList       数据
     * @param cnnameKeyMap   列名对应键值map（格式:  key: 中文名称, value: 数据查询列名）
     * @param titleLine      标题行，至少1开始
     * @param startWriteLine 内容开始写入行， 至少2开始
     * @param sheetPageSize  每页多少数据，默认一页500条
     * @return 临时文件地址
     */
    public static String addDataToExcel(InputStream inputStream, List<Map<String, Object>> dataList,
                                        Map<String, Object> cnnameKeyMap,
                                        int titleLine, int startWriteLine,
                                        Integer sheetPageSize) throws Exception {
        OutputStream outputStream = null;
        HSSFWorkbook book = null;
        try {
            // 数据键值转换为大写存储
            List<Map<String, Object>> data = convertDataKeyUpperCase(dataList);
            // 创建临时输出文件
            String filename = UUID.randomUUID().toString() + ".xls";
            String saveDir = System.getProperty("user.dir") + File.separator + "temp";
            File pa = new File(saveDir);
            if (!pa.exists()) {
                pa.mkdirs();
            }
            // 临时文件路径
            String savepath = saveDir + File.separator + filename;
            log.info("----- addDataToExcel file: " + savepath + " , data size:" + data.size() + " ----- ");
            book = new HSSFWorkbook(inputStream);
            HSSFSheet sheet = book.getSheetAt(0);
            HSSFRow titleRow = sheet.getRow(titleLine - 1);
            // 整理表格标题和数据key值对应关系
            List<DmForm> orderlyColumnList = getOrderlyColumnList(titleRow, cnnameKeyMap);
            outputStream = new FileOutputStream(savepath);
            // 写入数据到输出文件中
            writeDataToXlsFile(book, sheet, sheet.getSheetName(), orderlyColumnList, data, outputStream, startWriteLine, sheetPageSize);
            return savepath;
        } catch (Exception e) {
            log.error("addDataToExcel exception", e);
            throw e;
        } finally {
            closeBook(book);
            closeOs(outputStream);
        }
    }

    private static List<DmForm> getOrderlyColumnList(HSSFRow titleRow, Map<String, Object> cnnameKeyMap) {
        short firstCellNum = titleRow.getFirstCellNum();
        short lastCellNum = titleRow.getLastCellNum();
        List<DmForm> dmForms = new ArrayList<>();
        for (short m = firstCellNum; m < lastCellNum; m++) {
            HSSFCell cell = titleRow.getCell(m);
            String cnname;
            if (cell == null || (cnname = cell.getStringCellValue()) == null) {
                dmForms.add(DmForm.builder().name(null).build());
                continue;
            }
            cnname = cnname.trim();
            DmForm dmForm = DmForm.builder()
                    .name(cnname)
                    .code(cnnameKeyMap.get(cnname) == null ?
                            null : cnnameKeyMap.get(cnname).toString().toUpperCase())
                    .build();
            dmForms.add(dmForm);
        }
        return dmForms;
    }

    private static List<Map<String, Object>> convertDataKeyUpperCase(List<Map<String, Object>> list) {
        List<Map<String, Object>> dataList = new ArrayList<>();
        if (list != null) {
            for (Map<String, Object> map : list) {
                Map<String, Object> newMap = new HashMap<>();
                for (String key : map.keySet()) {
                    Object value = map.get(key);
                    newMap.put(key.toUpperCase(), value);
                }
                dataList.add(newMap);
            }
        }
        return dataList;
    }

    public static String getCellValue(Cell cell) {
        String dateFormat = "yyyy-MM-dd";
        SimpleDateFormat sdf = new SimpleDateFormat(dateFormat);
        if (cell.getCellType() == CellType.STRING) {
            return cell.getStringCellValue();
        } else if (cell.getCellType() == CellType.NUMERIC) {
            if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                return sdf.format(cell.getDateCellValue());
            } else {
                Double strcell = cell.getNumericCellValue();
                DecimalFormat formatCell = (DecimalFormat) NumberFormat.getPercentInstance();
                //非整数类型
                if (strcell != strcell.longValue()) {
                    //处理含小数类型
                    formatCell.applyPattern("0.0000000000");
                    String val = formatCell.format(strcell).replaceAll("0+$", "");//去掉后面多余的0;
                    //如果以.结尾
                    if (val.endsWith(".")) {
                        val = val.substring(val.lastIndexOf("."), val.length());
                    }
                    return val;
                } else {
                    formatCell.applyPattern("0");
                    return formatCell.format(strcell);
                }
            }
        }
        //如果是公式类型
        else if (cell.getCellType() == CellType.FORMULA) {
            String value;
            try {
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    Date date = cell.getDateCellValue();
                    value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                } else {
                    value = String.valueOf(cell.getNumericCellValue());
                }

            } catch (IllegalStateException e) {
                value = String.valueOf(cell.getRichStringCellValue());
            }
            return value;
        } else if (cell.getCellType() == CellType.BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == CellType.ERROR) {
            return String.valueOf(cell.getErrorCellValue());
        } else {
            try {
                return cell.getStringCellValue();
            } catch (Exception e) {
                return String.valueOf(cell);
            }
        }
    }

    /**
     * 创建一列数据
     *
     * @param currentRow
     * @param textList
     */
    private static void creatRow(Row currentRow, List<String> textList) {
        if (textList != null && textList.size() > 0) {
            int i = 0;
            for (String cellValue : textList) {
                Cell cell = currentRow.createCell(i++);
                cell.setCellValue(cellValue);
            }
        }
    }

    //设置单元格数据
    public static void setCell(HSSFSheet sheet, int row, int col, String value) {
        HSSFRow r = sheet.getRow(row - 1);
        if (r == null) {
            r = sheet.createRow(row - 1);
        }
        HSSFCell cell = r.getCell(col - 1);
        if (cell == null) {
            cell = r.createCell(col - 1);
            cell.setCellType(CellType.STRING);
            cell.setCellValue(value);
        } else {
            cell.setCellValue(value);
        }
    }

    /**
     * 判断行是否为空行
     */
    public static boolean isRowNull(HSSFRow row) {
        boolean bool = true;
        //row.getLastCellNum()+1
        HSSFCell cell1 = row.getCell(12);//福建双项实操模版过滤用
        for (Iterator iter = row.iterator(); iter.hasNext(); ) {
            HSSFCell cell = (HSSFCell) iter.next();
            String value = getCellValue(cell);
            //如果一个单元格有数据就返回
            if (value != null && !value.equals("") && !(cell.equals(cell1) && getCellValue(cell1).equals("60"))) {
                bool = false;
                break;
            }
        }
        return bool;
    }

    /**
     * Sheet复制
     */
    public static void copySheet(HSSFWorkbook wb, HSSFSheet fromSheet, HSSFSheet toSheet,
                                 boolean copyValueFlag) {
        // 合并区域处理
        mergerRegion(fromSheet, toSheet);
        for (Iterator<Row> rowIt = fromSheet.rowIterator(); rowIt.hasNext(); ) {
            HSSFRow tmpRow = (HSSFRow) rowIt.next();
            HSSFRow newRow = toSheet.createRow(tmpRow.getRowNum());
            //行复制
            copyRow(wb, tmpRow, newRow, copyValueFlag);
        }
    }

    /**
     * 行复制功能
     */
    public static void copyRow(SXSSFWorkbook wb, SXSSFRow fromRow, SXSSFRow toRow, boolean copyValueFlag) {
        int i = 0;
        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            SXSSFCell tmpCell = (SXSSFCell) cellIt.next();
            SXSSFCell newCell = toRow.createCell(i);
            copyCell(wb, tmpCell, newCell, copyValueFlag);
            i++;
        }
    }

    public static void copyRow(HSSFWorkbook wb, HSSFRow fromRow, HSSFRow toRow, boolean copyValueFlag) {
        int i = 0;
        for (Iterator<Cell> cellIt = fromRow.cellIterator(); cellIt.hasNext(); ) {
            HSSFCell tmpCell = (HSSFCell) cellIt.next();
            HSSFCell newCell = toRow.createCell(i);
            copyCell(wb, tmpCell, newCell, copyValueFlag);
            i++;
        }
    }

    /**
     * 复制原有sheet的合并单元格到新创建的sheet
     */
    public static void mergerRegion(SXSSFSheet fromSheet, SXSSFSheet toSheet) {
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(mergedRegionAt);
        }
    }

    public static void mergerRegion(HSSFSheet fromSheet, HSSFSheet toSheet) {
        int sheetMergerCount = fromSheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress mergedRegionAt = fromSheet.getMergedRegion(i);
            toSheet.addMergedRegion(mergedRegionAt);
        }
    }

    /**
     * 复制单元格
     *
     * @param srcCell
     * @param distCell
     * @param copyValueFlag true则连同cell的内容一起复制
     */
    public static void copyCell(SXSSFWorkbook wb, SXSSFCell srcCell, SXSSFCell distCell,
                                boolean copyValueFlag) {
        // 样式
        distCell.setCellStyle(srcCell.getCellStyle());
        // 评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {
                // nothing21
            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            } else { // nothing29
            }
        }
    }

    public static void copyCell(HSSFWorkbook wb, HSSFCell srcCell, HSSFCell distCell,
                                boolean copyValueFlag) {
        // 样式
        distCell.setCellStyle(srcCell.getCellStyle());
        // 评论
        if (srcCell.getCellComment() != null) {
            distCell.setCellComment(srcCell.getCellComment());
        }
        // 不同数据类型处理
        CellType srcCellType = srcCell.getCellType();
        distCell.setCellType(srcCellType);
        if (copyValueFlag) {
            if (srcCellType == CellType.NUMERIC) {
                if (DateUtil.isCellDateFormatted(srcCell)) {
                    distCell.setCellValue(srcCell.getDateCellValue());
                } else {
                    distCell.setCellValue(srcCell.getNumericCellValue());
                }
            } else if (srcCellType == CellType.STRING) {
                distCell.setCellValue(srcCell.getRichStringCellValue());
            } else if (srcCellType == CellType.BLANK) {
                // nothing21
            } else if (srcCellType == CellType.BOOLEAN) {
                distCell.setCellValue(srcCell.getBooleanCellValue());
            } else if (srcCellType == CellType.ERROR) {
                distCell.setCellErrorValue(srcCell.getErrorCellValue());
            } else if (srcCellType == CellType.FORMULA) {
                distCell.setCellFormula(srcCell.getCellFormula());
            } else { // nothing29
            }
        }
    }

    public static void createAndDownloadXExcel(HttpServletResponse response, String filename,
                                               String title, String sheetName,
                                               String titleKeys, List<Map<String, Object>> dataList,
                                               Integer pageSize) throws Exception {
        String filepath = createXExcel(title, sheetName, titleKeys, dataList, pageSize);
        FileUtil.downloadFile(response, filename, filepath, true);
    }

    private static String createXExcel(String title, String sheetName, String titleKeys, List<Map<String, Object>> dataList,
                                       Integer pageSize) throws Exception {
        List<DmForm> orderlyColumnList = getOrderlyColumnListByParam(titleKeys);
        // 数据键值转换为大写存储
        List<Map<String, Object>> data = convertDataKeyUpperCase(dataList);
        // 临时文件路径
        String savepath = createTmpFile(".xlsx");
        log.info("----- addDataToExcel file: " + savepath + " , data size:" + data.size() + " ----- ");
        SXSSFWorkbook book = new SXSSFWorkbook();
        SXSSFSheet sheet = book.createSheet(sheetName);
        int rowIndex = 0;
        if (title != null) {
            SXSSFRow row = sheet.createRow(rowIndex);
            // 合并单元格
            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, orderlyColumnList.size() - 1));
            SXSSFCell cell = row.createCell(0);
            cell.setCellValue(title);
            Font font = book.createFont();
            font.setFontName("Arial");
            font.setFontHeightInPoints((short) 13);//设置字体大小
            CellStyle cellStyle = book.createCellStyle();
            cellStyle.setFont(font);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            cell.setCellStyle(cellStyle);
            row.setHeight((short) 400);
            rowIndex++;
        }
        SXSSFRow row = sheet.createRow(rowIndex++);
        int index = 0;
        for (DmForm dmForm : orderlyColumnList) {
            SXSSFCell cell = row.createCell(index++);
            cell.setCellValue(dmForm.getName());
        }
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(savepath);
            // 写入数据到输出文件中
            writeDataToXlsxFile(book, sheet, sheetName, orderlyColumnList, data, outputStream, rowIndex + 1, pageSize);
        } finally {
            closeBook(book);
            closeOs(outputStream);
        }
        return savepath;
    }

    private static void writeDataToXlsxFile(SXSSFWorkbook workbook, SXSSFSheet sheet, String sheetName,
                                            List<DmForm> orderlyColumnList,
                                            List<Map<String, Object>> data, OutputStream outputStream,
                                            int startWriteLine, Integer sheetPageSize) throws Exception {
        sheetPageSize = sheetPageSize == null ? 500 : sheetPageSize;
        if (startWriteLine + sheetPageSize >= 65535) {
            throw new Exception("非法参数：写入行+页数太大");
        }
        CellStyle cellStyle2 = workbook.createCellStyle();
        DataFormat format = workbook.createDataFormat();
        cellStyle2.setDataFormat(format.getFormat("@"));
        int num = 0;
        int totalNo = 1;
        int sheet_index = 1;
        SXSSFRow startWriteRow = sheet.getRow(startWriteLine - 1);
        for (Iterator<Map<String, Object>> iterator = data.iterator(); iterator.hasNext(); ) {
            Map<String, Object> dataMap = iterator.next();
            if (num >= sheetPageSize) {//如果数据量超过页码数则新建一个sheet
                SXSSFSheet sheet1 = workbook.createSheet(sheetName + "(" + (++sheet_index) + ")");
                //合并区域处理
                mergerRegion(sheet, sheet1);
                // 设置表头
                for (int i = 0; i < startWriteLine; i++) {
                    SXSSFRow row = sheet1.createRow(i);
                    SXSSFRow row_master = sheet.getRow(i);
                    copyRow(workbook, row_master, row, true);
                    //设置行高
                    row.setHeight(row_master.getHeight());
                }
                //设置列宽
                for (int i = 0; i < orderlyColumnList.size(); i++) {
                    sheet1.setColumnWidth(i, sheet.getColumnWidth(i));
                }
                sheet = sheet1;
                num = 0;
            }
            SXSSFRow row = sheet.getRow(startWriteLine - 1 + num);
            if (row == null) {
                row = sheet.createRow(startWriteLine - 1 + num);
            }
            for (int i = 0; i < orderlyColumnList.size(); i++) {
                SXSSFCell cell = row.getCell(i);
                if (cell == null) {
                    cell = row.createCell(i);
                }
                cell.setCellType(CellType.STRING);//设置为字符串类型
                if (startWriteRow != null && startWriteRow.getCell(i) != null) {
                    cell.setCellStyle(startWriteRow.getCell(i).getCellStyle());//设置样式
                } else {
                    cell.setCellStyle(cellStyle2);
                }
                DmForm dmForm = orderlyColumnList.get(i);
                if (dmForm != null && "序号".equals(dmForm.getName())) {
                    if (dmForm.getCode() == null || dataMap.get(dmForm.getCode()) == null) {
                        cell.setCellValue(totalNo++);
                        continue;
                    }
                }
                if (dmForm == null || dmForm.getName() == null || dmForm.getCode() == null) {
                    cell.setCellValue("");
                    continue;
                }
                Object o = dataMap.get(dmForm.getCode());
                cell.setCellValue((o == null || o.equals("null")) ? "" : o.toString());//灌入值
            }
            num++;
        }
        workbook.write(outputStream);
        outputStream.flush();
    }

    @Data
    @Builder
    static class DmForm {
        private static final long serialVersionUID = -567619338401235118L;
        private String id;
        private String code;
        private String name;
        private String parentId;
    }
}
